package com.dfrz.dao;

import com.dfrz.entry.GoodsInfo;
import com.dfrz.util.JdbcUtils;
import com.mysql.cj.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

//增加商品
public class GoodsInfoDao {
    public int insertGoods(GoodsInfo goodsInfo){
        int count=0;
        String sqlStr="insert into res_goodsinfo(imgURL,name,describe,orprice,curprice,sellNumber,stock,smlltypeid,viewflag,bus_id,bigtypeid)" +
                " values(?,?,?,?,?,?,?,?,?,?,?)";
        QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        try {
            count=queryRunner.update(sqlStr,goodsInfo.getImgURL(),
                    goodsInfo.getName(),
                    goodsInfo.getDescribe(),
                    goodsInfo.getOrprice(),
                    goodsInfo.getCurprice(),
                    goodsInfo.getSellNumber(),
                    goodsInfo.getStock(),
                    goodsInfo.getSmalltypeid(),
                    goodsInfo.getViewflag(),
                    goodsInfo.getBus_id(),
                    goodsInfo.getBigtypeid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;

    }
    public int deleteGoodsByid(Integer id){
        int count=0;
        String sqlStr="update res_goodsinfo set status=-1 where id=?";
        QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        try {
            count=queryRunner.update(sqlStr,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    public int updateGoods(GoodsInfo goodsInfo){
        int count=0;
        QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        String sqlStr="update res_goodsinfo set id="+goodsInfo.getId();
        if (goodsInfo.getImgURL()!=null){
            sqlStr+=",imgURL="+goodsInfo.getImgURL();
        }
        if (goodsInfo.getName()!=null){
            sqlStr+=",name="+goodsInfo.getName();
        }
        if (goodsInfo.getDescribe()!=null){
            sqlStr+=",describe="+goodsInfo.getDescribe();
        }
        if (goodsInfo.getOrprice()!=null){
            sqlStr+=",orprice="+goodsInfo.getOrprice();
        }
        if (goodsInfo.getCurprice()!=null){
            sqlStr+=",curprice="+goodsInfo.getCurprice();
        }
        if (goodsInfo.getSellNumber()!=null){
            sqlStr+=",sellNumber="+goodsInfo.getSellNumber();
        }
        if (goodsInfo.getStock()!=null){
            sqlStr+=",stock="+goodsInfo.getStock();
        }
        if (goodsInfo.getSmalltypeid()!=null){
            sqlStr+=",smlltypeid="+goodsInfo.getSmalltypeid();
        }
        if (goodsInfo.getViewflag()!=null){
            sqlStr+=",viewflag="+goodsInfo.getViewflag();
        }
        if (goodsInfo.getBus_id()!=null){
            sqlStr+=",bus_id="+goodsInfo.getBus_id();
        }
        if (goodsInfo.getBigtypeid()!=null){
            sqlStr+=",bigtypeid="+goodsInfo.getBigtypeid();
        }

        sqlStr+="where id="+goodsInfo.getId();
        try {
            count=queryRunner.update(sqlStr);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;

    }
    public GoodsInfo queryGoodsById(int id){
        GoodsInfo goodsInfo=null;
        String sqlStr="select * from res_goodsinfo where id=?";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        try {
            goodsInfo=queryRunner.query(sqlStr,new BeanHandler<GoodsInfo>(GoodsInfo.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsInfo;
    }
    public List<GoodsInfo>queryGoodsByPage(String name,int limit,int startNum){
        List<GoodsInfo>listGoods=null;
        String sqlStr=null;
        if (!StringUtils.isNullOrEmpty(name)){
            sqlStr="select * from res_goodsinfo where name like '?' and status=1 order by sellNumber desc limit ?,? ";
            QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
            try {
                listGoods=queryRunner.query(sqlStr,new BeanListHandler<GoodsInfo>(GoodsInfo.class),name,startNum,limit);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else {
            sqlStr="select * from res_goodsinfo where status=1 order by sellNumber desc limit ?,?";
            QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
            try {
                listGoods=queryRunner.query(sqlStr,new BeanListHandler<GoodsInfo>(GoodsInfo.class),startNum,limit);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return listGoods;
    }
    public int queryCount(String name){
        long countL=0;
        String sqlStr=null;
        if (!StringUtils.isNullOrEmpty(name)){
            sqlStr="select * from res_goodsinfo where name like '?' and status=1 ";
            QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
            try {
                countL=queryRunner.query(sqlStr,new ScalarHandler<>(),name);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else {
            sqlStr="select * from res_goodsinfo where status=1 ";
            QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
            try {
                countL=queryRunner.query(sqlStr,new ScalarHandler<>());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (int) countL;
    }
    public List<GoodsInfo> queryGoodsByType(int typeId,int type){
        List<GoodsInfo> list=null;
        String sqlStr="";
        if(type==1){
            sqlStr="select * from res_gooodsinfo where smalltypeid=?";
        }else if(type==2){
            sqlStr="select * from res_gooodsinfo where bigtypeid=?";
        }
        QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        try {
            list=queryRunner.query(sqlStr,new BeanListHandler<GoodsInfo>(GoodsInfo.class),typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;


    }
    public List<GoodsInfo> getAllGoods(){
        List<GoodsInfo> list=null;
        String sqlStr="select * from res_goodsinfo";

        QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        try {
            list=queryRunner.query(sqlStr,new BeanListHandler<GoodsInfo>(GoodsInfo.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;

    }



}
